﻿/*
SELECT     BRIDGE.VantageID, BRIDGE.LoanClientID, CI.LoanID, BRIDGE.ApplicationNumber, BRIDGE.CompanyRef, BRIDGE.BranchCodeRef, 
                      BRIDGE.LoanProgramRef, BRIDGE.ProductRef, BRIDGE.JurisdictionRef, BRIDGE.PortfolioRef, BRIDGE.DealerName, BRIDGE.InterestTypeRef, 
                      BRIDGE.CurrentStatusRef, BRIDGE.CurrentStatusDate, BRIDGE.CollateralStatusRef, BRIDGE.CollateralStatusDate, BRIDGE.OriginalAmountFinanced, 
                      BRIDGE.AmountFinanced, BRIDGE.OpenDate, BRIDGE.FirstDueDate, BRIDGE.LoanTerm, BRIDGE.OriginalLoanTerm, BRIDGE.OriginalInterestRate, 
                      BRIDGE.InterestRate, BRIDGE.OriginalAPR, BRIDGE.APR, BRIDGE.OriginalDueDay, BRIDGE.DueDay, BRIDGE.OriginalPaymentAmount, 
                      BRIDGE.PaymentAmount, BRIDGE.PaymentFrequencyRef, BRIDGE.OriginalFinanceCharge, BRIDGE.FinanceCharge, BRIDGE.DiscountAmount, 
                      BRIDGE.OriginalReserve, BRIDGE.OriginalWithHold, BRIDGE.PurchaseDate, BRIDGE.PurchasePrincipalPaid, BRIDGE.PurchaseInterest, 
                      BRIDGE.PurchaseInterestPaid, BRIDGE.LoanFundingDate, BRIDGE.NextDueDate, BRIDGE.MaturityDate, BRIDGE.GrossBalance, 
                      BRIDGE.InterestAccrued, BRIDGE.InterestPaid, BRIDGE.PrinicpalPaid, BRIDGE.FeePaid, BRIDGE.PastDueInterest, BRIDGE.CurrentDueInterest, 
                      BRIDGE.PastDuePrincipal, BRIDGE.CurrentDuePrincipal, BRIDGE.NSFFeeBalance, BRIDGE.LateFeeBalance, BRIDGE.LegalFeeBalance, 
                      BRIDGE.PaymentMethodFeeBalance, BRIDGE.RepoFeeBalance, BRIDGE.InsuranceFeeBalance, BRIDGE.OtherFeeBalance, 
                      BRIDGE.DefermentFeeBalance, BRIDGE.DueDayChangeFeeBalance, BRIDGE.CollectionFeeBalance, BRIDGE.RecoveryFeeBalance, BRIDGE.Make, 
                      BRIDGE.Model, BRIDGE.ModelYear, BRIDGE.VIN, BRIDGE.CashPrice, BRIDGE.MilageAtPurchase, BRIDGE.GPSTrackerID, BRIDGE.BVAtPurchase, 
                      BRIDGE.Milage, BRIDGE.MilageAsOfDate, BRIDGE.BV, BRIDGE.BVAsOfDate, BRIDGE.TitleStateRef, BRIDGE.IsRegistered, BRIDGE.IsTitleReceived, 
                      BRIDGE.RelationshipTypeRef, BRIDGE.FirstName, BRIDGE.LastName, BRIDGE.FirstName + ' ' + BRIDGE.LastName AS ClientName, BRIDGE.SSN, BRIDGE.DLN, 
                      BRIDGE.DOB, BRIDGE.FICO, BRIDGE.MaritalStatusRef, BRIDGE.Occupation, BRIDGE.PrimaryLanguageRef, BRIDGE.TimeZoneRef, 
                      BRIDGE.EntityStatusRef, BRIDGE.Street, BRIDGE.AptSuite, BRIDGE.City, BRIDGE.CountyRef, BRIDGE.StateRef, BRIDGE.ZipCode, BRIDGE.DPD, 
                      BRIDGE.PastDueAmount, BRIDGE.LastPaymentDate, BRIDGE.DSLP, 
                      BRIDGE.GrossBalance + BRIDGE.PastDueAmount - BRIDGE.PastDuePrincipal AS PayOffAmount, BRIDGE.PaymentsMade, BRIDGE.PaymentsPerYear, 
                      BRIDGE.PrincipalBalance, BRIDGE.PerDiem
FROM         (SELECT     LoanID, BorrowerID, Pos
                       FROM          dbo.t_Loan_Borrower
                       WHERE      (Pos = 0)) AS L_B INNER JOIN
                      dbo.t_Borrower AS B ON L_B.BorrowerID = B.ID INNER JOIN
                      dbo.t_CarInfo AS CI ON L_B.LoanID = CI.LoanID FULL OUTER JOIN
                      vantage.v_Bridge_10 AS BRIDGE ON B.SSN = BRIDGE.SSN AND CI.VIN = BRIDGE.VIN
*/
CREATE VIEW vantage.v_Bridge
AS
SELECT     vantage.t_Bridge_20.VantageID, vantage.t_Bridge_20.CompanyID, vantage.t_Bridge_20.UsixID, vantage.t_Bridge_20.LoanClientID, 
                      ISNULL(vantage.t_Bridge_20.LoanID, 0) AS LoanID, vantage.t_Bridge_20.ApplicationNumber, vantage.t_Bridge_20.CompanyRef, 
                      vantage.t_Bridge_20.BranchCodeRef, vantage.t_Bridge_20.LoanProgramRef, vantage.t_Bridge_20.ProductRef, vantage.t_Bridge_20.JurisdictionRef, 
                      vantage.t_Bridge_20.PortfolioRef, vantage.t_Bridge_20.DealerName, vantage.t_Bridge_20.InterestTypeRef, vantage.t_Bridge_20.CurrentStatusRef, 
                      vantage.t_Bridge_20.CurrentStatusDate, vantage.t_Bridge_20.CollateralStatusRef, vantage.t_Bridge_20.CollateralStatusDate, 
                      vantage.t_Bridge_20.OriginalAmountFinanced, vantage.t_Bridge_20.AmountFinanced, vantage.t_Bridge_20.OpenDate, 
                      vantage.t_Bridge_20.FirstDueDate, vantage.t_Bridge_20.LoanTerm, vantage.t_Bridge_20.OriginalLoanTerm, vantage.t_Bridge_20.OriginalInterestRate, 
                      vantage.t_Bridge_20.InterestRate, vantage.t_Bridge_20.OriginalAPR, vantage.t_Bridge_20.APR, vantage.t_Bridge_20.OriginalDueDay, 
                      vantage.t_Bridge_20.DueDay, vantage.t_Bridge_20.OriginalPaymentAmount, vantage.t_Bridge_20.PaymentAmount, 
                      vantage.t_Bridge_20.PaymentFrequencyRef, vantage.t_Bridge_20.OriginalFinanceCharge, vantage.t_Bridge_20.FinanceCharge, 
                      vantage.t_Bridge_20.DiscountAmount, vantage.t_Bridge_20.OriginalReserve, vantage.t_Bridge_20.OriginalWithHold, 
                      vantage.t_Bridge_20.PurchaseDate, vantage.t_Bridge_20.PurchasePrincipalPaid, vantage.t_Bridge_20.PurchaseInterest, 
                      vantage.t_Bridge_20.PurchaseInterestPaid, vantage.t_Bridge_20.LoanFundingDate, vantage.t_Bridge_20.NextDueDate, 
                      vantage.t_Bridge_20.MaturityDate, vantage.t_Bridge_20.GrossBalance, vantage.t_Bridge_20.InterestAccrued, vantage.t_Bridge_20.InterestPaid, 
                      vantage.t_Bridge_20.PrinicpalPaid, vantage.t_Bridge_20.FeePaid, vantage.t_Bridge_20.PastDueInterest, vantage.t_Bridge_20.CurrentDueInterest, 
                      vantage.t_Bridge_20.PastDuePrincipal, vantage.t_Bridge_20.CurrentDuePrincipal, vantage.t_Bridge_20.NSFFeeBalance, 
                      vantage.t_Bridge_20.LateFeeBalance, vantage.t_Bridge_20.LegalFeeBalance, vantage.t_Bridge_20.PaymentMethodFeeBalance, 
                      vantage.t_Bridge_20.RepoFeeBalance, vantage.t_Bridge_20.InsuranceFeeBalance, vantage.t_Bridge_20.OtherFeeBalance, 
                      vantage.t_Bridge_20.DefermentFeeBalance, vantage.t_Bridge_20.DueDayChangeFeeBalance, vantage.t_Bridge_20.CollectionFeeBalance, 
                      vantage.t_Bridge_20.RecoveryFeeBalance, vantage.t_Bridge_20.Make, vantage.t_Bridge_20.Model, vantage.t_Bridge_20.ModelYear, 
                      vantage.t_Bridge_20.VIN, vantage.t_Bridge_20.CashPrice, vantage.t_Bridge_20.MilageAtPurchase, vantage.t_Bridge_20.GPSTrackerID, 
                      vantage.t_Bridge_20.BVAtPurchase, vantage.t_Bridge_20.Milage, vantage.t_Bridge_20.MilageAsOfDate, vantage.t_Bridge_20.BV, 
                      vantage.t_Bridge_20.BVAsOfDate, vantage.t_Bridge_20.TitleStateRef, vantage.t_Bridge_20.IsRegistered, vantage.t_Bridge_20.IsTitleReceived, 
                      vantage.t_Bridge_20.RelationshipTypeRef, vantage.t_Bridge_20.FirstName, vantage.t_Bridge_20.LastName, vantage.t_Bridge_20.ClientName, 
                      vantage.t_Bridge_20.SSN, vantage.t_Bridge_20.DLN, vantage.t_Bridge_20.DOB, vantage.t_Bridge_20.FICO, vantage.t_Bridge_20.MaritalStatusRef, 
                      vantage.t_Bridge_20.Occupation, vantage.t_Bridge_20.PrimaryLanguageRef, vantage.t_Bridge_20.TimeZoneRef, vantage.t_Bridge_20.EntityStatusRef, 
                      vantage.t_Bridge_20.Street, vantage.t_Bridge_20.AptSuite, vantage.t_Bridge_20.City, vantage.t_Bridge_20.CountyRef, vantage.t_Bridge_20.StateRef, 
                      vantage.t_Bridge_20.ZipCode, vantage.t_Bridge_20.DPD, vantage.t_Bridge_20.PastDueAmount, vantage.t_Bridge_20.LastPaymentDate, 
                      vantage.t_Bridge_20.DSLP, vantage.t_Bridge_20.PayOffAmount, vantage.t_Bridge_20.PaymentsMade, vantage.t_Bridge_20.PaymentsPerYear, 
                      vantage.t_Bridge_20.PrincipalBalance, usix.t_CustomerFile_Export.[Total Principal] AS U_TotalPrincipal, vantage.t_Bridge_20.PerDiem, 
                      usix.t_CustomerFile_Export.[Status Code] AS U_StatusCode, usix.t_CustomerFile_Export.Chargeoff AS U_Chargeoff, 
                      usix.t_CustomerFile_Export.[Final Repo Date] AS U_FinalRepoDate, usix.t_CustomerFile_Export.LoanID AS U_LoanID, 
                      dbo.t_State.Name AS Status_Repo, dbo.t_Repossession.StatusDate AS StatusDate_Repo, vantage.v_CollateralAction_Last.CollateralEventRef, 
                      vantage.v_CollateralAction_Last.ActionDate AS CollateralActionDate, DATEDIFF(dd, vantage.v_CollateralAction_Last.ActionDate, GETDATE()) 
                      AS DaysInCollateralAction, usix.t_CustomerFile_Export.DPD AS U_DPD, usix.t_CustomerFile_Export.DSLP AS U_DSLP, 
                      vantage.t_Bridge_20.PhoneHome, vantage.t_Bridge_20.PhoneWork, vantage.t_Bridge_20.PhoneMobile, vantage.t_Bridge_20.PhoneOther, 
                      vantage.t_Bridge_20.DaysSinceLastNote, PTP.DaysTillClose AS PTP_DaysTillClose, PTP.DueDate AS PTP_DueDate, 
                      PTP.CreatedUser AS PTP_Agent
FROM         vantage.v_PtpStatus AS PTP INNER JOIN
                      vantage.v_PTP_LastActive ON PTP.ID = vantage.v_PTP_LastActive.ID RIGHT OUTER JOIN
                      vantage.t_Bridge_20 ON PTP.AccountNumber = vantage.t_Bridge_20.VantageID LEFT OUTER JOIN
                      vantage.v_CollateralAction_Last ON vantage.t_Bridge_20.VantageID = vantage.v_CollateralAction_Last.AccountNumber LEFT OUTER JOIN
                      dbo.t_State INNER JOIN
                      dbo.t_Repossession ON dbo.t_State.ID = dbo.t_Repossession.StatusID AND dbo.t_State.ID = dbo.t_Repossession.StatusID AND 
                      dbo.t_State.ID = dbo.t_Repossession.StatusID AND dbo.t_State.ID = dbo.t_Repossession.StatusID AND 
                      dbo.t_State.ID = dbo.t_Repossession.StatusID ON vantage.t_Bridge_20.LoanID = dbo.t_Repossession.LoanID LEFT OUTER JOIN
                      usix.t_CustomerFile_Export ON vantage.t_Bridge_20.CompanyID = usix.t_CustomerFile_Export.CompanyID_Raw AND 
                      vantage.t_Bridge_20.UsixID = usix.t_CustomerFile_Export.UsixID

GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[32] 4[33] 2[15] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1[40] 4[34] 3) )"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4[30] 2[40] 3) )"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4[50] 3) )"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1[49] 4) )"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4) )"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 1
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "PTP"
            Begin Extent = 
               Top = 220
               Left = 297
               Bottom = 459
               Right = 457
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "v_PTP_LastActive (vantage)"
            Begin Extent = 
               Top = 300
               Left = 505
               Bottom = 363
               Right = 656
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_Bridge_20 (vantage)"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 630
               Right = 251
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "v_CollateralAction_Last (vantage)"
            Begin Extent = 
               Top = 150
               Left = 573
               Bottom = 258
               Right = 777
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_State"
            Begin Extent = 
               Top = 6
               Left = 516
               Bottom = 148
               Right = 667
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_Repossession"
            Begin Extent = 
               Top = 9
               Left = 327
               Bottom = 117
               Right = 478
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_CustomerFile_Export (usix)"
            Begin Extent = 
               Top = 105
               Left = 617
               Bottom =', @level0type = N'SCHEMA', @level0name = N'vantage', @level1type = N'VIEW', @level1name = N'v_Bridge';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'462
               Right = 816
            End
            DisplayFlags = 280
            TopColumn = 85
         End
      End
   End
   Begin SQLPane = 
      PaneHidden = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 129
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 4215
         Alias = 1665
         Table = 2655
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'vantage', @level1type = N'VIEW', @level1name = N'v_Bridge';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 2, @level0type = N'SCHEMA', @level0name = N'vantage', @level1type = N'VIEW', @level1name = N'v_Bridge';

